CREATE PROCEDURE [dbo].[BAEActivityTypeGetAll] @UserId AS VARCHAR(15)
AS

/* previously: SELECT DISTINCT ACTIVITY_TYPE FROM Activity ORDER BY ACTIVITY_TYPE ASC;  */


DECLARE @ActivityAccessKeys TABLE
    (
    [PROD_TYPE] VARCHAR(10),
    [ACCESS_KEYWORDS] VARCHAR(255)
    )

DECLARE @ActivityAccessKeysSplit TABLE
    (
    [PROD_TYPE] VARCHAR(10),
    [ACCESS_KEYWORD] VARCHAR(60)
    )

DECLARE @UserAccessKeysSplit TABLE
    (
    [ACCESS_KEYWORD] VARCHAR(60)
    )

INSERT @ActivityAccessKeys (PROD_TYPE, ACCESS_KEYWORDS)
    SELECT Product_Type.PROD_TYPE, Product_Type.ACCESS_KEYWORDS FROM dbo.Product_Type

DECLARE thecursor CURSOR FAST_FORWARD FOR SELECT PROD_TYPE, ACCESS_KEYWORDS FROM @ActivityAccessKeys

DECLARE @PROD_TYPE VARCHAR(10)
DECLARE @ACCESS_KEYWORDS VARCHAR(255)
DECLARE @ACCESS_KEYWORD VARCHAR(60)
DECLARE @token VARCHAR(60)

OPEN thecursor

FETCH NEXT FROM thecursor INTO @PROD_TYPE, @ACCESS_KEYWORDS

WHILE @@FETCH_STATUS = 0
BEGIN
        IF (DATALENGTH (@ACCESS_KEYWORDS) = 0)
        BEGIN
            INSERT INTO @ActivityAccessKeysSplit ([PROD_TYPE], [ACCESS_KEYWORD])     VALUES (@PROD_TYPE, '')
        END

        -- assume comma delimiter, and eliminate the special case of the last key
        SET @ACCESS_KEYWORDS = @ACCESS_KEYWORDS + ','

        WHILE (DATALENGTH (@ACCESS_KEYWORDS) > 0)
        BEGIN
            SET @token = LTRIM(RTRIM(SUBSTRING (@ACCESS_KEYWORDS, 1, CHARINDEX (',', @ACCESS_KEYWORDS) - 1)))
            SET @ACCESS_KEYWORDS = SUBSTRING (@ACCESS_KEYWORDS, CHARINDEX (',', @ACCESS_KEYWORDS) + 1, 255)

            IF (DATALENGTH(@token) > 0 AND @token <> ',')
            BEGIN
                INSERT INTO @ActivityAccessKeysSplit ([PROD_TYPE], [ACCESS_KEYWORD])   VALUES (@PROD_TYPE, @token)
            END
        END

FETCH NEXT FROM thecursor INTO @PROD_TYPE, @ACCESS_KEYWORDS

END

CLOSE thecursor
DEALLOCATE thecursor

SELECT @ACCESS_KEYWORDS = LTRIM(RTRIM(Users.AccessKeywords)) + ',' FROM [dbo].[Users] WHERE Users.UserId = @UserId

WHILE (DATALENGTH (@ACCESS_KEYWORDS) > 0)
BEGIN
    SET @token = LTRIM(RTRIM(SUBSTRING (@ACCESS_KEYWORDS, 1, CHARINDEX (',', @ACCESS_KEYWORDS) - 1)))
    SET @ACCESS_KEYWORDS = SUBSTRING (@ACCESS_KEYWORDS, CHARINDEX (',', @ACCESS_KEYWORDS) + 1, 255)

    IF (DATALENGTH(@token) > 0 AND @token <> ',')
    BEGIN
        INSERT INTO @UserAccessKeysSplit ([ACCESS_KEYWORD])   VALUES (@token)
    END
END

INSERT INTO @UserAccessKeysSplit ([ACCESS_KEYWORD])   VALUES ('') -- user has to be able to join to Activity types with blank access keywords    


SELECT aa.PROD_TYPE ACTIVITY_TYPE
FROM @ActivityAccessKeysSplit aa
INNER JOIN @UserAccessKeysSplit ua
ON aa.ACCESS_KEYWORD = ua.ACCESS_KEYWORD

GO